<?php

class PondstatisticHelper extends AppHelper {

	function GetOpenTotal($month){
		$start = $this->GetMonthStart($month);
		$end = $this->GetMonthEnd($month);
		$status1 = 33;
		$status2 = 35;
		$result = mysql_query("SELECT count(*) AS count FROM pondcases WHERE status_id IN ('$status1','$status2') AND createdate BETWEEN '$start' AND '$end'");
		$count = mysql_fetch_object($result)->count;
		return $count;	
	}
	
	function GetClosedTotal($month){
		$start = $this->GetMonthStart($month);
		$end = $this->GetMonthEnd($month);
		$status1 = 34;
		$status2 = 36;
		$status3 = 37;
		$status4 = 42;
		$result = mysql_query("SELECT count(*) AS count FROM pondcases WHERE status_id IN ('$status1','$status2','$status3','$status4') AND createdate BETWEEN '$start' AND '$end'");
		$count = mysql_fetch_object($result)->count;
		return $count;	
	}
	
	function GetOpenDet1($month){
		$start = $this->GetMonthStart($month);
		$end = $this->GetMonthEnd($month);
		$status1 = 33;
		$status2 = 35;
		$result = mysql_query("SELECT * FROM pondcases WHERE status_id IN ('$status1','$status2') AND createdate BETWEEN '$start' AND '$end'");
		$counter = 0;
		WHILE($row = mysql_fetch_array($result))
		  {
			$date = $row["createdate"];
			IF($this->getAging($date) == 1){
			  	$counter++;
			  }
		  }
		return $counter;  
	}
	
	function GetOpenDet2($month){
		$start = $this->GetMonthStart($month);
		$end = $this->GetMonthEnd($month);
		$status1 = 33;
		$status2 = 35;
		$result = mysql_query("SELECT * FROM pondcases WHERE status_id IN ('$status1','$status2') AND createdate BETWEEN '$start' AND '$end'");
		$counter = 0;
		WHILE($row = mysql_fetch_array($result))
		  {
			$date = $row["createdate"];
			IF($this->getAging($date) == 2){
			  	$counter++;
			  }
		  }
		return $counter;
	}
	
	function GetOpenDet3($month){
		$start = $this->GetMonthStart($month);
		$end = $this->GetMonthEnd($month);
		$status1 = 33;
		$status2 = 35;
		$result = mysql_query("SELECT * FROM pondcases WHERE status_id IN ('$status1','$status2') AND createdate BETWEEN '$start' AND '$end'");
		$counter = 0;
		WHILE($row = mysql_fetch_array($result))
		  {
			$date = $row["createdate"];
			IF($this->getAging($date) == 3){
			  	$counter++;
			  }
		  }
		return $counter;
	}
	
	function GetOpenDet4($month){
		$start = $this->GetMonthStart($month);
		$end = $this->GetMonthEnd($month);
		$status1 = 33;
		$status2 = 35;
		$result = mysql_query("SELECT * FROM pondcases WHERE status_id IN ('$status1','$status2') AND createdate BETWEEN '$start' AND '$end'");
		$counter = 0;
		WHILE($row = mysql_fetch_array($result))
		  {
			$date = $row["createdate"];
			IF($this->getAging($date) == 4){
			  	$counter++;
			  }
		  }
		return $counter;
	}
	
	function GetClosedDet1($month){
		$start = $this->GetMonthStart($month);
		$end = $this->GetMonthEnd($month);
		$status1 = 34;
		$status2 = 36;
		$status3 = 37;
		$status4 = 42;
		$result = mysql_query("SELECT * FROM pondcases WHERE status_id IN ('$status1','$status2','$status3','$status4') AND createdate BETWEEN '$start' AND '$end'");
		$counter = 0;
		WHILE($row = mysql_fetch_array($result))
		  {
		  $create = $row["createdate"];
		  	$process = $row["processdate"];
			IF($this->DaysBetween($create,$process) == 1){
			  	$counter++;
			}
		  }
		return $counter;
	}
	
	function GetClosedDet2($month){
		$start = $this->GetMonthStart($month);
		$end = $this->GetMonthEnd($month);
		$status1 = 34;
		$status2 = 36;
		$status3 = 37;
		$status4 = 42;
		$result = mysql_query("SELECT * FROM pondcases WHERE status_id IN ('$status1','$status2','$status3','$status4') AND createdate BETWEEN '$start' AND '$end'");
		$counter = 0;
		WHILE($row = mysql_fetch_array($result))
		  {
		  $create = $row["createdate"];
		  	$process = $row["processdate"];
			IF($this->DaysBetween($create,$process) == 2){
			  	$counter++;
			}
		  }
		return $counter;
	}
	
	function GetClosedDet3($month){
		$start = $this->GetMonthStart($month);
		$end = $this->GetMonthEnd($month);
		$status1 = 34;
		$status2 = 36;
		$status3 = 37;
		$status4 = 42;
		$result = mysql_query("SELECT * FROM pondcases WHERE status_id IN ('$status1','$status2','$status3','$status4') AND createdate BETWEEN '$start' AND '$end'");
		$counter = 0;
		WHILE($row = mysql_fetch_array($result))
		  {
		  $create = $row["createdate"];
		  	$process = $row["processdate"];
			IF($this->DaysBetween($create,$process) == 3){
			  	$counter++;
			}
		  }
		return $counter;
	}
	
	function GetClosedDet4($month){
		$start = $this->GetMonthStart($month);
		$end = $this->GetMonthEnd($month);
		$status1 = 34;
		$status2 = 36;
		$status3 = 37;
		$status4 = 42;
		$result = mysql_query("SELECT * FROM pondcases WHERE status_id IN ('$status1','$status2','$status3','$status4') AND createdate BETWEEN '$start' AND '$end'");
		$counter = 0;
		WHILE($row = mysql_fetch_array($result))
		  {
			$create = $row["createdate"];
		  	$process = $row["processdate"];
			IF($this->DaysBetween($create,$process) == 4){
			  	$counter++;
			}
		  }
		return $counter;
	}
	
	function GetMonthStart($month){
		$datum = mktime(0, 0, 0, $month, 1, date("Y"));
		$start = date("Y-m-d H:i:s",$datum);
		return $start;	
	}
	
	function GetMonthEnd($month){
		$date = strtotime($this->GetMonthStart($month));
		$datum = mktime(23, 59, 59, $month, date("t",$date), date("Y"));
		$end = date("Y-m-d H:i:s",$datum);
		return $end;
	}
	
	function getAging($date){
		$timedb = strtotime($date);
		$now = time();
		$differenz = round((($now - $timedb) / 60 / 60 / 24),0); 
		
		IF($differenz > 0 && $differenz < 8) { return 1; }
		ELSEIF($differenz > 7 && $differenz < 15) { return 2; }
		ELSEIF($differenz > 14 && $differenz < 22) { return 3; }
		ELSEIF($differenz > 21) { return 4; }
	}
	
	function DaysBetween($create,$process){
		$createdate = strtotime($create);
		$processdate = strtotime($process);  
		
		$differenz = round((($processdate - $createdate) / 60 / 60 / 24),0);
		
		IF($differenz >= 0 && $differenz < 8) { return 1; }
		ELSEIF($differenz > 7 && $differenz < 15) { return 2; }
		ELSEIF($differenz > 14 && $differenz < 22) { return 3; }
		ELSEIF($differenz > 21) { return 4; }
	}
	
}

?>
